--    Author    : ChenErHao
--    Name      : ADM.RPT_LOAB_CLASS.HQL
--    Functions : 固定报表_贷款余额分类统计
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-16  ChenErHao       1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE ADM.RPT_LOAB_CLASS PARTITION (DATA_DATE = '#V_DATA_DATE#',DATA_SRC_ORG) 

-- 行业_合计
 SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,JRTJ_AREA_CODE                                                                  -- 集中系统地区编码
   ,SUM(LOAN_BALANCE) AS AMOUNT                                                  -- 贷款余额
   ,'A01' AS SUBJECT_SEQ                                                          -- 指标序号
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAB_INFO 
 WHERE  DATA_DATE ='#V_DATA_DATE#' AND CUSTOMER_TYPE IN ('1','0')
 GROUP BY 
   DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,JRTJ_AREA_CODE
 
UNION ALL

-- 分行业
 SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,JRTJ_AREA_CODE                                                                  -- 集中系统地区编码
   ,SUM(LOAN_BALANCE) AS AMOUNT                                                  -- 贷款余额
   ,CASE WHEN LPIC_SUB='A' THEN 'A02'
         WHEN LPIC_SUB='B' THEN 'A03'
         WHEN LPIC_SUB='C' THEN 'A04'
         WHEN LPIC_SUB='D' THEN 'A05'
         WHEN LPIC_SUB='E' THEN 'A06'
         WHEN LPIC_SUB='F' THEN 'A07'
         WHEN LPIC_SUB='G' THEN 'A08'
         WHEN LPIC_SUB='H' THEN 'A09'
         WHEN LPIC_SUB='I' THEN 'A10'
         WHEN LPIC_SUB='J' THEN 'A11'
         WHEN LPIC_SUB='K' THEN 'A12'
         WHEN LPIC_SUB='L' THEN 'A13'
         WHEN LPIC_SUB='M' THEN 'A14'
         WHEN LPIC_SUB='N' THEN 'A15'
         WHEN LPIC_SUB='O' THEN 'A16'
         WHEN LPIC_SUB='P' THEN 'A17'
         WHEN LPIC_SUB='Q' THEN 'A18'
         WHEN LPIC_SUB='R' THEN 'A19'
         WHEN LPIC_SUB='S' THEN 'A20'
         WHEN LPIC_SUB='T' THEN 'A21'
         WHEN LPIC_SUB='2' THEN 'A22'
         WHEN LPIC_SUB='1' THEN 'A23'
       ELSE '9999'
    END AS SUBJECT_SEQ                                                            -- 指标序号
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAB_INFO 
 WHERE  DATA_DATE ='#V_DATA_DATE#' AND CUSTOMER_TYPE IN ('1','0')
 GROUP BY 
   DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,LPIC_SUB
  ,JRTJ_AREA_CODE

UNION ALL

--企业规模
 SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,JRTJ_AREA_CODE                                                                  -- 集中系统地区编码
   ,SUM(LOAN_BALANCE) AS AMOUNT                                                  -- 贷款余额
   ,'B24' SUBJECT_SEQ                                                             -- 指标序号
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAB_INFO 
 WHERE DATA_DATE ='#V_DATA_DATE#' AND ENTERPRISE_SCALE IN('CS01','CS02','CS03','CS04')
 GROUP BY 
   DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,JRTJ_AREA_CODE
 
UNION ALL

--企业规模
 SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,JRTJ_AREA_CODE                                                                  -- 集中系统地区编码
   ,SUM(LOAN_BALANCE) AS AMOUNT                                                  -- 贷款余额
   ,CASE WHEN ENTERPRISE_SCALE='CS01' THEN 'B25'
         WHEN ENTERPRISE_SCALE='CS02' THEN 'B26'
         WHEN ENTERPRISE_SCALE='CS03' THEN 'B27'
         WHEN ENTERPRISE_SCALE='CS04' THEN 'B28'
       ELSE '9999'
    END AS SUBJECT_SEQ                                                            -- 指标序号
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAB_INFO 
 WHERE DATA_DATE ='#V_DATA_DATE#' AND ENTERPRISE_SCALE IN('CS01','CS02','CS03','CS04')
 GROUP BY 
   DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,ENTERPRISE_SCALE
  ,JRTJ_AREA_CODE
 
UNION ALL

--农村贷款_合计
 SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,JRTJ_AREA_CODE                                                                  -- 集中系统地区编码
   ,SUM(LOAN_BALANCE) AS AMOUNT                                                  -- 贷款余额
   ,'C29' AS SUBJECT_SEQ                                                        -- 指标序号
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAB_INFO 
 WHERE DATA_DATE ='#V_DATA_DATE#' 
       AND SUBSTR(REGIST_ADD_CODE,5,1) NOT IN ('1','0')
       AND ENTERPRISE_SCALE IN ('CS01','CS02','CS03','CS04','CS05')
 GROUP BY 
   DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,JRTJ_AREA_CODE
 
UNION ALL

--农村贷款_企业贷款
 SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_BALANCE) AS AMOUNT                                                  -- 贷款余额
   ,'C30' AS SUBJECT_SEQ                                                         -- 指标序号
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAB_INFO 
 WHERE DATA_DATE ='#V_DATA_DATE#' 
       AND SUBSTR(REGIST_ADD_CODE,5,1) NOT IN ('1','0')
       AND ENTERPRISE_SCALE IN ('CS01','CS02','CS03','CS04')
 GROUP BY 
   DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,JRTJ_AREA_CODE
 
UNION ALL

--农村贷款_各组织贷款
 SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE           -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,JRTJ_AREA_CODE                                                                  -- 集中系统地区编码
   ,SUM(LOAN_BALANCE) AS AMOUNT                                                  -- 贷款余额
   ,'C31' AS SUBJECT_SEQ                                                        -- 指标序号
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAB_INFO 
 WHERE DATA_DATE ='#V_DATA_DATE#' 
       AND SUBSTR(REGIST_ADD_CODE,5,1) NOT IN ('1','0')
       AND ENTERPRISE_SCALE IN ('CS05')
 GROUP BY 
   DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,JRTJ_AREA_CODE
;
